from typing import List

from sam import import_csv_path
from sam.app.base import single_stats, sentiments, cut_stats_export, cut_stats
from sam.util.dbUtil import mysql_env_config_dict, DB
from sam.util.fileUtil2 import read_file, write_file_quick, write_multi_sheet_excel_quick
from sam.util.strUtil import filter_symbol_4_word


class NetMusicLyricAnalysis(object):

    def __init__(self):
        super().__init__()
        self.db = DB(mysql_env_config_dict.get("net"), level="debug")

    @staticmethod
    def fetch_all_keywords_from_file() -> List[str]:
        line_list = read_file(import_csv_path)
        return [line[0].strip() for line in line_list if line[0].strip()]

    def fetch_one_keyword_all_video_uuid(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="bili_list"
            , column_str="uuid"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [query_result[0] for query_result in query_result_list]

    def fetch_num_by_keyword(self, keyword):
        query_sql = f" select sum(watch_num) total_watch_num  " \
                    f" from bili_list_old where keyword in ('{keyword}') "
        query_result_dict_list = self.db.query_by_sql(
            query_sql=query_sql
            , column=['total_watch_num']
            , optional="dict"
        )
        query_result_dict = query_result_dict_list[0]
        return query_result_dict['total_watch_num']

    def fetch_lyric_by_keyword(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="net_music"
            , column_str="lyric"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [filter_symbol_4_word(query_result[0]) for query_result in query_result_list]

    def fetch_singer_by_keyword(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="net_music"
            , column_str="singer"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [filter_symbol_4_word(query_result[0]) for query_result in query_result_list]

    def fetch_comment_by_keyword(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="net_music"
            , column_str="comment"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [filter_symbol_4_word(query_result[0]) for query_result in query_result_list]

    @staticmethod
    def lyric_word_analysis(lyric: str):
        word_count = single_stats(list(lyric))
        lyric_len = len(lyric)
        repeat_len = lyric_len - len(word_count.items())
        return lyric_len, repeat_len

    @staticmethod
    def lyric_sentiments_analysis(lyric: str):
        result = sentiments(lyric)
        return "正向" if result > 0.5 else '负向'

    @staticmethod
    def lyric_cut_analysis(lyric_list: list):
        cut_stats_export(line_list=lyric_list, export_file_name="50首歌词歌词分词与统计")

    def builder_table_content(self):
        """
        歌曲 歌词总字数 重复字数 情感得分	歌手	b站视频总播放量
        """
        data_list = [['歌曲', '歌词总字数', '重复字数', '情感得分', '歌手', 'b站视频总播放量']]
        keyword_list = self.fetch_all_keywords_from_file()
        for keyword in keyword_list:
            singer_str = self.fetch_singer_by_keyword(keyword)[0]
            lyric_str = self.fetch_lyric_by_keyword(keyword)[0]
            lyric_len, repeat_len = self.lyric_word_analysis(lyric_str)
            sentiments_str = self.lyric_sentiments_analysis(lyric_str)
            total_watch_num = self.fetch_num_by_keyword(keyword)
            data_list.append(
                [keyword, lyric_len, repeat_len, sentiments_str, singer_str, total_watch_num]
            )
            print(f"完成关键词: {keyword}")
        else:
            write_file_quick(
                data_list=data_list
                , export_file_name="50首歌歌词分析"
                , optional="cover"
                , file_type="xlsx"
            )

    def song_comment_analysis(self):
        """
        top10评论
        """
        keyword_list = self.fetch_all_keywords_from_file()
        sheet_name_content_map = {}
        whole_comment_list = []
        i = 0
        for keyword in keyword_list:
            i += 1
            comment_list = self.fetch_comment_by_keyword(keyword)
            whole_comment_list.extend(comment_list)
            if i > 10:
                continue
            else:
                stats_result = cut_stats(line_list=comment_list)
                sheet_name_content_map[keyword] = stats_result
                print(f"完成关键词: {keyword}")
        else:
            stats_result = cut_stats(line_list=whole_comment_list)
            sheet_name_content_map["50首歌整体分析"] = stats_result

        write_multi_sheet_excel_quick(
            sheet_name_content_map=sheet_name_content_map
            , export_file_name="50首歌网易云音乐评论分词与统计"
        )

    def main(self):
        self.builder_table_content()
        self.song_comment_analysis()


def net_music_lyric_analysis():
    process = NetMusicLyricAnalysis()
    process.main()


if __name__ == "__main__":
    net_music_lyric_analysis()
